﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_ImportOrderClearance : Form
    {
        private INISettings mySettings;
        private SqlConnection myConnection;

        public frm_Add_ImportOrderClearance()
        {
            InitializeComponent();
            this.mySettings = new INISettings();
            this.myConnection = new SqlConnection(mySettings.SQLConnectionString);
            LoadClearanceList();
        }

        /////////////////////////////////////
        /// Load Clearance List
        /////////////////////////////////////
        private void LoadClearanceList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT ID FROM [241_CustomClearance] ORDER BY ID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["ID"].ToString();
                    item.Value = dataReader["ID"].ToString();
                    this.clearanceNo.Properties.Items.Add(item);
                }
                dataReader.Close();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }                
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {            
            bool duplicate = false;
            if (this.clearanceNo.SelectedIndex < 0)
            {
                MessageBox.Show("Please select Clearance No.");
            }
            else
            {
                ComboboxItem item = (ComboboxItem)this.clearanceNo.SelectedItem;
                //Check Duplicate
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT COUNT(*) FROM [241_ImportOrderClearance]"
                        + " WHERE ClearanceNo='" + item.Value.ToString() + "' AND OrderNo='" + this.orderNo.Text + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader[0] != DBNull.Value)
                        {
                            if (Int32.Parse(dataReader[0].ToString()) > 0)
                            {
                                duplicate = true;
                            }
                        }
                    }
                    dataReader.Close();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    duplicate = true;
                }

                if (duplicate)
                {
                    MessageBox.Show("The Clearance No.: " + item.Value.ToString() + " AND Order No.: " + this.orderNo.Text + " existed");
                }
                else
                {
                    try
                    {
                        myConnection.Open();
                        string sql = "INSERT INTO [241_OrderClearanceTemp] "
                                + "(ClearanceNo, OrderNo) "
                                + "VALUES ('" + item.Value.ToString() + "', '" + this.orderNo.Text + "')";                                
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "INSERT INTO [241_ImportOrderClearance] ([ClearanceNo], [OrderNo], [PartNo],"
                            + " [Quanity], [IQID], [UsingUnit], [BuyingUnit], [Using/IQEx], [Buying/IQEx])"
                            + " SELECT t1.ClearanceNo, t1.OrderNo, t2.[PartNo],"
                            + " (t2.[Quantity] - ISNULL(t3.Quanity, 0)) AS Quantity, t2.[IQID], t2.[UsingUnit], t2.[BuyingUnit],"
                            + " t2.[Using/IQEx], t2.[Buying/IQEx] FROM [241_OrderClearanceTemp] AS t1 LEFT OUTER JOIN"
                            + " [VPartReceiveUnion] AS t2 ON t1.OrderNo COLLATE Japanese_Unicode_CI_AI=t2.OrderNo LEFT OUTER JOIN"
                            + " [241_ImportOrderClearance] AS t3 ON t3.OrderNo=t1.OrderNo"
                            + " WHERE  (t2.[Quantity] - ISNULL(t3.Quanity, 0)) > 0";
                        cmd_sql.CommandTimeout = 30000;
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "DELETE FROM [241_OrderClearanceTemp]";
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();


                        myConnection.Close();
                        MessageBox.Show("Order-Clearance's added Success!");
                        //this.Dispose();
                        clearForm();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot add Order-Clearance. Please try again!");
                    }
                }
            }
        }

        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }
    }
}
